const Service = require('egg').Controller;

class AppointmentsService extends Service {

    async findByUserId() {
        const { ctx } = this;
        const user_id = ctx.params.user_id; // 假设从URL参数中获取user_id
        // 执行 SQL JOIN 查询
        const sql = `
          SELECT a.*, u.name AS user_name, u.avatar AS user_avatar, d.name AS doctor_name, d.avatar AS doctor_avatar, d.major AS doctor_major
          FROM appointments AS a
          LEFT JOIN users AS u ON a.user_id = u.id
          LEFT JOIN users AS d ON a.doctor_id = d.id
          WHERE a.user_id = ?
        `;
        
        try {
          return await ctx.app.mysql.query(sql, [user_id]);
        } catch (error) {
          return { success: false, message: '查询出错', error: error.message };
        }
    }


    async allList() {
        const { ctx } = this;
        const sql = `
          SELECT a.*, u.name AS user_name, u.avatar AS user_avatar, d.name AS doctor_name, d.avatar AS doctor_avatar, d.major AS doctor_major
          FROM appointments AS a
          LEFT JOIN users AS u ON a.user_id = u.id
          LEFT JOIN users AS d ON a.doctor_id = d.id
        `;
        
        try {
          return await ctx.app.mysql.query(sql);
        } catch (error) {
          return { success: false, message: '查询出错', error: error.message };
        }
    }
    async create() {
        const { ctx } = this;
        const { doctor_id, user_id,details, appointment_time } = ctx.request.body;
        const id = doctor_id == undefined?ctx.request.body.id:ctx.request.body.doctor_id;
        return await ctx.app.mysql.insert('appointments', {
          doctor_id:id,
          details,
          user_id,
          appointment_time
        });
      }
    
      // Retrieve all appointments
      async list() {
        const { ctx } = this;
        return await ctx.app.mysql.select('appointments');
      }
    
      // Retrieve a single appointment
      async read() {
        const { ctx } = this;
        const id = ctx.params.doctor_id == undefined?ctx.params.id:ctx.params.doctor_id;
        console.log('??????医生id呢？',id,ctx.params.doctor_id == true);
        return await ctx.app.mysql.get('appointments', { id });
      }
    
      async readoctor() {
          const { ctx } = this;
          const doctor_id = ctx.params.doctor_id;
          const id = doctor_id== undefined?ctx.params.id:doctor_id;
          const appointment = await ctx.app.mysql.get('appointments', { doctor_id:id });

          console.log('??????医生id呢？',doctor_id);

          const sql = `
          SELECT a.*, u.name AS user_name, u.avatar AS user_avatar, d.name AS doctor_name, d.avatar AS doctor_avatar, d.major AS doctor_major
          FROM appointments AS a
          LEFT JOIN users AS u ON a.user_id = u.id
          LEFT JOIN users AS d ON a.doctor_id = d.id
          WHERE d.id = ?  
        `;
        return await ctx.app.mysql.query(sql, [id]);
       
      }

      // Update an appointment
      async update() {
        const { ctx } = this;
        const id = ctx.params.id;
        const { doctor_id, details, appointment_time } = ctx.request.body;
        const options = {
          where: { id } // options to tell which row to update
        };
        return await ctx.app.mysql.update('appointments', {
          doctor_id,
          details,
          appointment_time
        }, options);
       
      }
    
      // Delete an appointment
      async delete() {
        const { ctx } = this;
        const id = ctx.params.id;
        return await ctx.app.mysql.delete('appointments', { id });
      
      }
}
module.exports = AppointmentsService;
